USE PhoneEvolution


GO
CREATE PROC spGetAllCompanies
	AS
	SELECT * FROM dbo.Company
	
	
GO
CREATE PROC spGetAllPhones
	AS
	SELECT * FROM dbo.Phone
GO
CREATE PROC spGetPhoneById
@phoneId int
	AS
	SELECT * FROM dbo.Phone WHERE Id = @phoneId
	
	
GO
CREATE PROC spGetPhonesByCompanyId
@companyId int
	AS
	SELECT * FROM dbo.Phone WHERE CompanyId = @companyId
	
	
GO
CREATE PROC spLogin
@username varchar(50),
@password varchar(50)
	AS
	SELECT Username, [Password] From dbo.[User] WHERE Username = @username AND Password = @password
	
	
GO
CREATE PROC spSearchInAllPhones
@keyWord nvarchar(200),
@priceFrom float,
@priceTo float,
@type int
	AS
	IF (@type = 0)
	BEGIN
		SELECT * FROM Phone WHERE Name LIKE '%'+@keyWord+'%'
	END
	ELSE IF(@type = 1)
	BEGIN
		SELECT * FROM Phone WHERE Name LIKE '%'+@keyWord+'%' AND PRICE >= @priceFrom
	END
	ELSE
	BEGIN
		SELECT * FROM Phone WHERE Name like '%'+@keyWord+'%' AND PRICE >= @priceFrom AND PRICE < @priceTo
	END
GO
	
CREATE PROC spSearchByCompanyId
@companyId int,
@keyWord varchar(200),
@priceFrom float,
@priceTo float,
@type int
	AS
	IF (@type = 0)
	BEGIN
		SELECT * FROM Phone WHERE Name LIKE '%'+@keyWord+'%' AND CompanyId = @companyId
	END
	ELSE IF(@type = 1)
	BEGIN
		SELECT * FROM Phone WHERE Name LIKE '%'+@keyWord+'%' AND PRICE >= @priceFrom AND CompanyId = @companyId
	END
	ELSE
	BEGIN
		SELECT * FROM Phone WHERE Name like '%'+@keyWord+'%' AND PRICE >= @priceFrom AND PRICE < @priceTo AND CompanyId = @companyId
	END
Go
